﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OracleClient;
using System.Windows.Forms;

namespace Projekt_GUI
{
    public class OracleHelper
    {
        public static OracleHelper Instance = new OracleHelper("localhost", "PROJEKT", "PROJEKT");

        public OracleConnection Connection { get; private set; }
        public bool IsConnected { get { return this.Connection.State == System.Data.ConnectionState.Open; } }

        public System.Data.ConnectionState State { get { return this.Connection.State; } }

        public OracleCommand LastCommand { get; private set; }

        public OracleHelper(string dataSource, string user, string pw)
        {
            try
            {
                this.Connection = new OracleConnection(string.Format("Data Source={0}; User Id={1}; Password={2}", dataSource, user, pw));
            }
            catch (OracleException ex)
            {
                MessageBox.Show(ex.Message, "Oracle Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        public void Open()
        {
            if (!IsConnected)
                this.Connection.Open();
        }

        public void Close()
        {
            if (IsConnected)
                this.Connection.Close();
        }

        public OracleDataReader Select(string attributes, string table, string where = "", string bonus = "")
        {
            if (this.Connection != null)
            {
                this.LastCommand = this.Connection.CreateCommand();

                string cmd = string.Format("SELECT {0} FROM {1}", attributes, table);
                if (where != "")
                    cmd += " WHERE " + where;
                if (bonus != "")
                    cmd += " " + bonus;

                this.LastCommand.CommandText = string.Format(cmd);

                return LastCommand.ExecuteReader();
            }
            else return null;
        }
        public void Delete(string table, string where, bool auto = true)
        {
            try
            {
                if (IsConnected)
                {
                    if (!auto) Connection.Open();
                    OracleCommand com = Connection.CreateCommand();
                    com.CommandText = string.Format("DELETE FROM {0} WHERE {1}", table, where);

                    com.ExecuteNonQuery();
                    if (!auto) Connection.Close();
                }
                else MessageBox.Show("Keine Verbindung");
            }
            catch (OracleException ex)
            {
                MessageBox.Show(ex.Message, "Oracle Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }

        }
        public void Insert(string table, string attributes, string values, bool auto = true)
        {
            try
            {
                if (IsConnected)
                {
                    if (auto) Connection.Open();
                    OracleCommand com = Connection.CreateCommand();
                    com.CommandText = string.Format("INSERT INTO {0} ({1}) VALUES ({2})", table, attributes, values);

                    com.ExecuteNonQuery();
                    if (auto) Connection.Close();
                }
                else MessageBox.Show("Keine Verbindung");
            }
            catch (OracleException ex)
            {
                MessageBox.Show(ex.Message, "Oracle Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
        // Bsp:
        // Update(tabelle, "id = 1, Name = 'dasda')
        // Update(tabelle, "Name = 'dasda', "id = 1")
        // Update(tabelle, "Name = 'dasda', "id = 1", true, "SORT BY Name")
        public void Update(string table, string set, string where = "", bool auto = true, string bonus = "")
        {
            try
            {
                if (IsConnected)
                {
                    if (auto) Connection.Open();
                    OracleCommand com = Connection.CreateCommand();
                    string cmd = string.Format("UPDATE {0} SET {1}", table, set);
                    if (where != "")
                        cmd += " WHERE " + where;
                    if (bonus != "")
                        cmd += " " + bonus;
                    com.CommandText = cmd;

                    com.ExecuteNonQuery();
                    if (!auto) Connection.Close();
                }
                else MessageBox.Show("Keine Verbindung");
            }
            catch (OracleException ex)
            {
                MessageBox.Show(ex.Message, "Oracle Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
    }
}
